package pkg.genericity.DAO;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

import pkg.genericity.DO.Criteria;
import pkg.genericity.utils.Utils;

/**
 * 
 * @author Farrukh Shakil
 *
 */
public abstract class JDBCDAO {
	
	private static String defaulDbColType, dbDrivers, dbUri, dbUsername, dbPassword;
	private Logger log = Logger.getLogger(JDBCDAO.class.getName());
	static {
		ResourceBundle bundle = ResourceBundle.getBundle("pkg.genericity.app");
		defaulDbColType=bundle.getString("default.db.col.type");
		dbDrivers=bundle.getString("db.drivers");
		dbUri=bundle.getString("db.uri");
		dbUsername=bundle.getString("db.username");
		dbPassword=bundle.getString("db.password");
	}

	/**
	 * Returns connection to database
	 * @return
	 */
	protected Connection getConnection()
	{
		try {
			Class.forName(dbDrivers);
		} catch (ClassNotFoundException e) {
			log.error("Unable to find the jdbc drivers. Please add jdbc jar file to the classpath");
			log.error("",e);
		}
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(dbUri,dbUsername,dbPassword);
		} catch (SQLException e) {
			log.error("Unable to establish connection");
			e.printStackTrace();
			log.error("",e);
		}
		return connection;
	}
	
	
	/**
	 * Executes query and returns the results of the query
	 * @param query
	 * @param includeHeaders
	 * @return
	 */
	protected List<Object[]> executeQuery(String query, Boolean includeHeaders)
	{
		Connection connection = getConnection();
		List<Object[]> list =null;
		PreparedStatement pstat=null;
		
		if (connection == null) {
			log.error("connection is null. returning null from method");
			return null;
		}
		try {
			pstat = connection.prepareStatement(query);
			log.debug("Executing Query: "+query);
			list = getTable(pstat.executeQuery(),includeHeaders);
			pstat.close();
			connection.close();
		} catch (SQLException e) {
			log.error("",e);
			e.printStackTrace();
		}
		return list;
	}
	
	
	/**
	 * Helper method for executeQuery. Takes the result set and returns a List of Records
	 * @param rs
	 * @param includeHeaders
	 * @return
	 * @throws SQLException
	 */
	private List<Object[]> getTable(ResultSet rs, Boolean includeHeaders) throws SQLException 
	 {
		log.info("Entering getTable()");
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colCount = rsmd.getColumnCount();
	    List<Object[]> list = new ArrayList<Object[]>();
	    Object obj;
	    while (rs.next()) {
	    	Object []record = new Object[colCount];
	    	if (includeHeaders) {
	    		Object []headers = new Object[colCount];
	    		int colNo=0;
	    		while (colNo++ < colCount) {
	    			obj=rsmd.getColumnName(colNo);
	    			headers[colNo-1]=obj;
	    		}
	    	list.add(headers);
	    	includeHeaders=false;
	    	}
	    	
	    	for (int colNo = 1; colNo <= colCount; colNo++) {
	    		if((obj=rs.getObject(colNo))!=null) {
	    			record[colNo-1] = obj;	    			
	    		}
	    	}
	    	list.add(record);
	    }
	   log.info("Exiting getTable()");
	   return list;
	 }
	
	
	/**
	 * Executes an update statement
	 * @param query
	 * @return
	 */
	protected Integer executeUpdate(String query)
	{
		Connection connection = getConnection();
		PreparedStatement pstat = null;
		Integer recordsUpdated=0;
		try {
			log.debug("Executing Update: "+query);
			pstat = connection.prepareStatement(query);
			recordsUpdated= pstat.executeUpdate();
			pstat.close();
			connection.close();
		} catch (SQLException e) {
			log.error("",e);
			e.printStackTrace();
		}
		return recordsUpdated;
	}
	
	/**
	 * Executes queries in a single batch
	 * @param queries
	 * @return
	 */
	protected Integer executeBatchUpdate(String []queries)
	{
		Connection connection = getConnection();
		Integer recordsUpdated=0;
		try {
			connection.setAutoCommit(false);
			Statement stat = connection.createStatement();
			for (String query:queries) {
				log.debug("Executing Batch Query :"+query);
				stat.addBatch(query);
			}
			stat.executeBatch();
			stat.close();
			connection.close();
		} catch (SQLException e) {
			log.error("",e);
			e.printStackTrace();
		}
		return recordsUpdated;
	}
	
	
	/**
	 * Returns Maximum available value for Id field
	 * @param tableName
	 * @return
	 */
	public Integer getMaxId(String tableName) {
		List<Object[]> list = executeQuery("SELECT MAX(id) FROM " + tableName,false);
		if (list.size() != 0) {
			if (list.get(0)[0] != null) {
				return Integer.valueOf(list.get(0)[0].toString().trim());
			} else {
				log.info("Array at zero location of list for getMaxId is null");
			}
		} else {
			log.info("list size for getMaxId is zero");
		}
		return 0;
	}
	
	/**
	 * Get all columns in the given table
	 * @param tableName
	 * @return
	 */
	public String[] getColumnNames(String tableName) {
		List<Object[]> list = executeQuery("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='"+tableName.toUpperCase()+"' ORDER BY ORDINAL_POSITION", false);
		String []arr = new String[list.size()];
		int i=0;
		for (Object[] objects:list) {
			arr[i++]=objects[0].toString();
		}
		return arr;
	}
	
	
	/**
	 * Alters column in a  table
	 * @param tableName
	 * @param add
	 * @param arr
	 * @return
	 */
	public Integer alterCols(String tableName, Boolean add, String []arr)
	{
		int i=0;
		String []sqls = new String[arr.length];
		for (String s:arr){
			sqls[i++]=("ALTER TABLE "+tableName+	 ((add)?" ADD COLUMN "+s+" "+defaulDbColType:" DROP COLUMN "+s));
		}
		return executeBatchUpdate(sqls);
	}

	/**
	 * For logging purpose ,Outputs the query results to console
	 * @param query
	 */
	public void displayResults(String query)
	{
		List<Object[]> table= executeQuery(query, true);
		for (Object[] row:table) {
			 String newRow="";
			for (Object col:row) {
				newRow+=col+"\t";
			}
			System.out.println(newRow);
			log.debug(newRow);
		}
	}
	
	/**
	 * if table exists in the database or not
	 * @param tableName
	 * @return
	 */
	public boolean isTableExist(String tableName)
	{
		Connection con = getConnection();
		try {
			//return con.getMetaData().getColumnPrivileges(null, null, tableName, null).next();
			PreparedStatement pstat = con.prepareStatement("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='"+tableName.toUpperCase()+"'");
			ResultSet rs =pstat.executeQuery();
			return rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * Creates a new table in the database. Currently only one database type for all columns is allowed
	 * @param tableName
	 * @param colNames
	 * @param type
	 * @return
	 */
	public boolean createTable(String tableName, String []colNames, String type)
	{
		if (type == null) {
			type = defaulDbColType;
		}
		String sql = "CREATE TABLE "+tableName+" ("+Utils.createCSVStr(colNames,type)+")";
		return executeUpdate(sql) > 0;
	}
	
	/**
	 * Drops a table
	 * @param tableName
	 * @return
	 */
	public boolean dropTable(String tableName)
	{
		return executeUpdate("DROP TABLE "+tableName) >0 ;
	}

	
	/**
	 * Builds a Select * statement
	 * @param clazz
	 * @return
	 */
	public String buildSelectStatement(String tableName)
	{
		return "SELECT * FROM "+tableName;
	}

	
	/**
	 * Helper method for buildInsertStatement Method
	 * @param tablename
	 * @param queryFieldNamesPart
	 * @param queryValuesPart
	 * @return
	 */
	protected String prepareInsertQuery(String tablename, String queryFieldNamesPart, String queryValuesPart)
	{
		return "INSERT INTO "+tablename+" ( " + queryFieldNamesPart+") VALUES("+queryValuesPart+")";
	}
}
